﻿CREATE PROCEDURE [dbo].[proc_Finance_Record_Create]
	(
		@CreateDate nchar(10)
	)
AS
Begin
	--公用变量
	Declare @A decimal(18,2),@B decimal(18,2),@C decimal(18,2),@D decimal(18,2),@E decimal(18,2),@F decimal(18,2),@G decimal(18,2),
			@H decimal(18,2),@I decimal(18,2),@J decimal(18,2),@K decimal(18,2),@L decimal(18,2),@M decimal(18,2),@N decimal(18,2),
			@O decimal(18,2),@P decimal(18,2),@Q decimal(18,2),@R decimal(18,2),@S decimal(18,2),@T decimal(18,2),
			@U decimal(18,2),@V decimal(18,2),@W decimal(18,2),@X decimal(18,2),@Y decimal(18,2),@Z decimal(18,2)

	--公司表变量
	Declare @indextable Table(id int Identity(1,1),nid int,CompanyName nvarchar(50))
	Insert Into @indextable(nid,CompanyName) Select Id,CompanyName From Company
	
	--循环插入更新数据
	Declare @start int, @end int, @today int, @yesterday int, @companyId int ,@companyName nvarchar(50)
	Select  @end=Count(Id) From @indextable
	Select  @start=1
	While(@start<=@end)
		begin
			Select @companyId=nid,@companyName=CompanyName From @indextable Where Id=@start
			Select @today=Count(Id) From Finance_Record Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate--公司当日数据是否存在
			Select @yesterday=Count(Id) From Finance_Record Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=Convert(nchar(10),Dateadd(d,-1,@CreateDate),120)--公司昨日数据是否存在
			
			Set @A=ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=@companyId and PaymentTypeId=1),0)---全部现金收入
			Set @B=ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=@companyId and PaymentTypeId<>1),0)--全部银行收入
			Set @C=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=@companyId and PaymentTypeId=1),0)---全部现金支出
			Set @D=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=@companyId and PaymentTypeId<>1),0)--全部银行支出
			Set @E=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=1 and PaymentTypeId=1 and Convert(nchar(10),CurrentDate,120)=@CreateDate),0)---当日平帐现金收入
			Set @F=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=1 and PaymentTypeId<>1 and Convert(nchar(10),CurrentDate,120)=@CreateDate),0)--当日平帐银行收入
			Set @G=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=2 and PaymentTypeId=1 and Convert(nchar(10),CurrentDate,120)=@CreateDate),0)---当日平帐现金支出
			Set @H=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=2 and PaymentTypeId<>1 and Convert(nchar(10),CurrentDate,120)=@CreateDate),0)--当日平帐银行支出
			Set @I=ISNULL((Select YYXJ From Finance_Record Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=Convert(nchar(10),DateAdd(d,-1,@CreateDate),120)),0)--昨日应有现金
			Set @J=ISNULL((Select YYYH From Finance_Record Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=Convert(nchar(10),DateAdd(d,-1,@CreateDate),120)),0)--昨日应有银行
			Set @K=ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=@companyId and PaymentTypeId=1 and Convert(nchar(10),TradeDate,120)=@CreateDate),0)---当日收入现金
			Set @L=ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=@companyId and PaymentTypeId<>1 and Convert(nchar(10),TradeDate,120)=@CreateDate),0)--当日收入银行
			Set @M=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=@companyId and PaymentTypeId=1 and (Convert(nchar(10),CreateDate,120))=@CreateDate),0)---当日支出现金
			Set @N=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=@companyId and PaymentTypeId<>1 and (Convert(nchar(10),CreateDate,120))=@CreateDate),0)--当日支出银行
			Set @O=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=@companyId and SubjectId=203 and Convert(nchar(10),CreateDate,120)=@CreateDate),0)--当日借款（借款科目203）
			Set @P=ISNULL((Select SYXJ From Finance_Record Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate),0)--当日实有现金
			Set @Q=ISNULL((Select SYYH From Finance_Record Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate),0)--当日实有银行
			Set @R=ISNULL((Select Sum(YingShouPrice-ShiShouPrice) From [Order] Where CompanyId=@companyId),0)----应收合计
			Set @S=ISNULL((Select Sum(CostPrice) From Project Where CompanyId=@companyId and FinanceAudit=1),0)--应付合计
			Set @T=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and TypeId=1 and Convert(nchar(10),CreateDate,120)=@CreateDate),0)--新增毛利
			Set @U=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and TypeId=2 and Convert(nchar(10),CreateDate,120)=@CreateDate),0)--续费毛利
			Set @V=ISNULL((Select Sum(YingShouPrice-ShiShouPrice) From [Order] Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate),0)--新增应收
			Set @W=ISNULL((Select Sum(CostPrice) From Project Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate and FinanceAudit=1),0)--新增应付
			Set @X=ISNULL((Select Sum(YingShouPrice) From [Order] Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate),0)--成单金额
			Set @Y=ISNULL((Select Sum(Prices) From Order_PaymentRecord Where CompanyId=@companyId and SWId=2 and Convert(nchar(10),FinanceDate,120)=@CreateDate),0)--尾款收入
			Set @Z=0--尾款收入
			
			if(@today=0)
				begin
					Insert Into Finance_Record
					(
						 CompanyId
						,CompanyName
						,YYXJ
						,YYYH
						,JKHJ
						,ZJHJ
						,YYXJPZ
						,YYYHPZ
						,CE
						,YSHJ
						,YFHJ
						,XZML
						,XFML
						,XZYS
						,XZYF
						,CDJE
						,WKSR
						,FYLX
						,CreateDate
					)
					Values
					(
						 @companyId--公司ID
						,@companyName--公司名称
						,Case When @yesterday=0 Then (@A-@C)+(@E-@G) Else @I+(@K-@M)+(@E-@G) End--应有现金
						,Case When @yesterday=0 Then (@B-@D)+(@F-@H) Else @J+(@L-@N)+(@F-@H) End--应有银行
						,@O--当日借款
						,Case When @yesterday=0 Then (@A+@B-@C-@D)+(@E+@F-@G-@H) Else (@I+@J)+(@K+@L-@M-@N)+(@E+@F-@G-@H) End--资金合计
						,(@E-@G)--应有现金平整
						,(@F-@H)--应有银行平整
						,(@P+@Q)-(@K+@L-@M-@N)--差额
						,@R--应收合计
						,@S--应付合计
						,@T--新增毛利
						,@U--续费毛利
						,@V--新增应收
						,@W--新增应付
						,@X--成单金额
						,@Y--尾款合计
						,@Z--非营利性
						,GetDate()
					)
				end
			else 
				begin
					Update Finance_Record Set
						 YYXJ=Case When @yesterday=0 Then (@A-@C)+(@E-@G) Else @I+(@K-@M)+(@E-@G) End--应有现金
						,YYYH=Case When @yesterday=0 Then (@B-@D)+(@F-@H) Else @J+(@L-@N)+(@F-@H) End--应有银行
						,JKHJ=@O--当日借款
						,ZJHJ=Case When @yesterday=0 Then (@A+@B-@C-@D)+(@E+@F-@G-@H) Else (@I+@J)+(@K+@L-@M-@N)+(@E+@F-@G-@H) End--资金合计
						,YYXJPZ=(@E-@G)--应有现金平整
						,YYYHPZ=(@F-@H)--应有银行平整
						,CE=(@P+@Q)-(@K+@L-@M-@N)--差额
						,YSHJ=@R--应收合计
						,YFHJ=@S--应付合计
						,XZML=@T--新增毛利
						,XFML=@U--续费毛利
						,XZYS=@V--新增应收
						,XZYF=@W--新增应付
						,CDJE=@X--成单金额
						,WKSR=@Y--尾款合计
						,FYLX=@Z--非营利性
					Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate
				end
			Set @start=@start+1--自增
		end
End
	RETURN